﻿using System;
using Devart.Data.MySql;
using System.Net.NetworkInformation;

namespace dgmobile
{
  /// <summary>
  /// All powerful MySQL key holder and path clearer.
  /// Uses Devart crap.
  /// That is way too expensive to license.
  /// So I stole it.
  /// HAHA.
  /// Yes.
  /// Carry on...
  /// </summary>
  public static class DB
  {
    //The main MySQL connection object.
    private static MySqlConnection connection;

    //MySQL fetcher.
    private static MySqlDataReader reader;

    //MySQL query string builder? Or just a glorified string? IDK.
    private static MySqlCommand query;

    //Bridge to display data on dataGridView. Super neat.
    private static MySqlDataAdapter adapter;

    //Collection only used to determine wether proxy is needed or not.
    private static NetworkInterface[] network = NetworkInterface.GetAllNetworkInterfaces();

    /// <summary>
    /// Static constructor for basic types of class.
    /// </summary>
    static DB()
    {
      //Initializes most components.
      connection = new MySqlConnection();
      query = new MySqlCommand();
      query.Connection = connection;
      connection.ConnectionString =
        "Server=codebad.org;Database=dgmobile;User=dg;Password=dgsql";

      //Needed for telemar DNS - that network SUCKS!
      connection.ConnectionTimeout = 10;

      //Determining if behind firewall and setting proxy for dat!
      foreach(NetworkInterface adapt in network)
      {
        if(adapt.GetIPProperties().DnsSuffix == "telemar")
        {
          connection.ProxyOptions.Host = "10.21.7.9";
          connection.ProxyOptions.Port = 83;
        }
      }
    }

    /// <summary>
    /// Connection opener for MySQL DB.
    /// </summary>
    /// <returns></returns>
    public static bool Connect()
    {
      try
      {
        connection.Open();
        return true;
      }
      catch
      {
        return false;
      }
    }

    /// <summary>
    /// Attempts to close MySQL connection.
    /// </summary>
    /// <returns></returns>
    public static bool Disconnect()
    {
      try
      {
        connection.Close();
        return true;
      }
      catch
      {
        return false;
      }
    }

    /// <summary>
    /// Return user from DB based on given ID.
    /// </summary>
    /// <param name="chapa"></param>
    /// <returns></returns>
    public static User GetUser(string chapa)
    {
      query.CommandText = "SELECT * FROM users WHERE chapa='" + chapa + "'";

      if(Connect())
      {
        reader = query.ExecuteReader();

        if(reader.Read())
        {
          User result = new User
          {
            ID = reader.GetInt32("id"),
            Chapa = reader.GetString("chapa"),
            Nome = reader.GetString("nome"),
            DG = reader.GetString("dg")
          };

          Disconnect();

          return result;
        }
      }

      return null;
    }

    /// <summary>
    /// Data from form to DB. EZ.
    /// </summary>
    /// <param name="item"></param>
    public static bool InsertData(Data item)
    {
      string command =
        "INSERT INTO data (userid, stamp, type, terminal, estacao, meta, responsible, " +
        "status, observation) VALUES(" +
        item.UserID.ToString() + ", " +
        "\"" + System.DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss") + "\", " +
        "\"" + item.Type + "\", " +
        "\"" + item.Terminal + "\", " +
        "\"" + item.Station + "\", " +
        "\"" + item.Meta + "\", " +
        "\"" + item.Responsible + "\", " +
        "\"" + item.Status + "\", " +
        "\"" + item.Observation.Replace(Environment.NewLine, " ") + "\"" +
        ")";

      query.CommandText = command;

      if(Connect())
      {
        query.ExecuteNonQuery();
        Disconnect();
        return true;
      }
      else
      {
        return false;
      }
    }

    /// <summary>
    /// Creates table from data to populate grid.
    /// </summary>
    /// <param name="grid"></param>
    public static System.Data.DataTable ListData(bool monthly = false)
    {
      string lowerLimit = "";
      string upperLimit = "";
      string q = "";

      //Setting limits and query start.
      if(monthly)
      {
        DateTime holder = DateTime.Today;
        lowerLimit = new DateTime(holder.Year, holder.Month, 1).ToString("dd/MM/yyyy") + " 00:00:00"; ;
        upperLimit = new DateTime(holder.Year, holder.Month, DateTime.DaysInMonth(holder.Year, holder.Month)).ToString("dd/MM/yyyy") + " 23:59:59";

        q = "SELECT SUBSTRING_INDEX(data.stamp, ' ', 1) as 'Data', " +
          "SUBSTR(stamp, LOCATE(' ', stamp)+1) as 'Hora', " +
          "UPPER(users.nome) as 'Operador', UPPER(users.dg) as 'DG', ";
      }
      else
      {
        lowerLimit = DateTime.Now.ToString("dd/MM/yyyy") + " 00:00:00";
        upperLimit = DateTime.Now.ToString("dd/MM/yyyy") + " 23:59:59";

        q = "SELECT data.id as 'id', data.stamp as 'Data / Hora', " +
          "UPPER(CONCAT(SUBSTRING_INDEX(users.nome, ' ', 1), ' - ' , users.dg)) as 'Operador', ";
      }

      //Big ass string finish.
      q += "UPPER(data.type) as 'Atividade', UPPER(data.responsible) as 'Responsável', " +
          "UPPER(data.terminal) as 'Terminal', UPPER(data.estacao) as 'Estação', " +
          "UPPER(data.meta) as 'Detalhe', UPPER(data.status) as 'Situação', " +
          "UPPER(data.observation) as 'Observação' " +
          "FROM data INNER JOIN users ON users.id=data.userid " +
          "WHERE STR_TO_DATE(data.stamp, '%d/%m/%Y %T') " +
          "BETWEEN STR_TO_DATE('" + lowerLimit + "', '%d/%m/%Y %T') " +
          "AND STR_TO_DATE('" + upperLimit + "', '%d/%m/%Y %T') ";

      q += (monthly) ? "ORDER BY data.stamp ASC" : "ORDER BY data.stamp DESC";

      try
      {
        adapter = new MySqlDataAdapter(q, connection);
        System.Data.DataSet set = new System.Data.DataSet();
        adapter.Fill(set);
        return set.Tables[0];
      }
      catch
      {
        return null;
      }
    }

    /// <summary>
    /// Deletes single entry from data based on selection on dataGridView
    /// </summary>
    /// <param name="id"></param>
    public static bool DeleteData(int id)
    {
      string command = "DELETE FROM data WHERE id=" + id;

      query.CommandText = command;

      if(Connect())
      {
        query.ExecuteNonQuery();
        Disconnect();
        return true;
      }
      else
      {
        return false;
      }
    }
  }
}